package jsyf.oa.service.report.expense;

import com.alibaba.fastjson.JSONObject;
import jsyf.com.util.DateUtils;
import jsyf.com.util.FastJsonConvert;
import jsyf.oa.dao.report.expense.ReportCostDao;
import jsyf.oa.facade.report.expense.ReportCostFacade;
import jsyf.oa.service.report.reportCommon.ReportCommonMethodsAndConstant;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Calendar;
import java.util.List;

/**
 * Created by Administrator on 2018/4/4.
 */
@Service
@Transactional(rollbackFor = Exception.class)
@com.alibaba.dubbo.config.annotation.Service(protocol = {"dubbo"})
public class ReportCostService implements ReportCostFacade {

    @Autowired
    private ReportCostDao reportCostDao;

    @Autowired
    private ReportCommonMethodsAndConstant common;

    @Override
    public List<JSONObject> getForPersonalCostByUserIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "  t_sys_user.ID AS userId,\n" +
                "  t_sys_user.`NAME` AS userName,\n" +
                "\tt_wrt_cost.CREATE_USER_ID AS createUserId,\n" +
                "\tt_wrt_cost.COST_SMALL_NAME AS costSmallName,\n" +
                "\tt_wrt_cost.INVOICE_FLAG AS invoiceFlag,\n" +
                "\tt_wrt_cost.`START` AS bxState,\n" +
                "\tt_wrt_cost.REPORT_DATE AS date,\n" +
                "\tIFNULL(`t_wrt_cost`.`MONEY`, 0) AS `money`,\n" +
                "\tt_wrt_cost_detail.MILEAGE AS mileage,\n" +
                "\tt_wrt_cost_detail.IMG AS img,\n" +
                "\tt_wrt_cost_detail.LICENSE as license,\n" +
                "\tt_wrt_cost_detail.HOTEL_NAME AS hotelName,\n" +
                "\tt_wrt_cost_detail.HOTEL_TEL AS hotelTel,\n" +
                "\tt_wrt_cost_detail.RESIDENCE AS residence,\n" +
                "\tt_wrt_cost_detail.`HOST` AS hostUser,\n" +
                "\tt_wrt_cost_detail.GUESTS AS guests,\n" +
                "\tt_wrt_cost_detail.GUEST_NATURE AS guestNature,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_NAME AS restaurantName,\n" +
                "\tt_wrt_cost_detail.RESTAURANT_TEL AS restaurantTel,\n" +
                "\tt_wrt_cost_detail.ENTERTAINMENT_VENUE AS entertainmentVenue,\n" +
                "\tt_wrt_cost_detail.PHONE AS phone,\n" +
                "\tt_wrt_cost_detail.BEFORE_OFFICE AS beforeOffice,\n" +
                "\tt_wrt_cost_detail.AFTER_OFFICE AS afterOffice,\n" +
                "\tt_wrt_cost_detail.OFFICE_MATTERS AS officemetters,\n" +
                "\tt_wrt_cost_detail.PREDICT_MONEY AS predictMoney,\n" +
                "\tt_wrt_cost.REMARK AS remark\n" +
                "FROM t_wrt_cost\n" +
                "LEFT JOIN t_wrt_cost_detail ON t_wrt_cost.ID = t_wrt_cost_detail.FK_COST_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID = t_wrt_cost.CREATE_USER_ID\n" +
                "WHERE t_wrt_cost.CREATE_USER_ID = '"+userId+"'\n" +
                "AND t_wrt_cost.REPORT_DATE = '"+dateTime+"'");
        return reportCostDao.queryForJsonList(sql.toString());
    }

    @Override
    public List<JSONObject> getForPersonalByUserIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        String start = jsonParams.getString("start");
        String sql = "select v_sys_part_time_for_report.roleId from\n" +
                "v_sys_part_time_for_report LEFT JOIN t_sys_role_name on t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                "WHERE 1=1  AND v_sys_part_time_for_report.userId='" + userId + "'\n" +
                "AND t_sys_role_name.FRONT_FLAG=1";
        List<JSONObject> jsonObjects = reportCostDao.queryForJsonList(sql);
        Boolean flag = false;
        Calendar c = Calendar.getInstance();
        String todayTime = DateUtils.parse(c.getTime(), "yyyy-MM-dd");
        for (JSONObject json : jsonObjects) {
            if (json.containsKey("roleId") && !json.getString("roleId").isEmpty()) {
                flag = true;
                break;
            }
        }
        if (flag) {
            return reportCostDao.getForPersonalByUserIdAndDateTime(userId, dateTime, start);
        } else {
            return reportCostDao.getExceptPersonalByUserIdAndDateTime(userId, dateTime, start,todayTime);
        }
    }

    @Override
    public List<JSONObject> getForDepartByUserIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT sysUser.*,IFNULL(sysCost.money,0) AS money,IFNULL(sysCost.bxMoney,0) AS bxMoney,IFNULL(sysCost.unBxMoney,0) AS unBxMoney\n" +
                "FROM\n" +
                "(\n" +
                "SELECT userId,departName,roleName,userName,regionName,'" + dateTime + "' as reportDate\n" +
                "FROM v_sys_user_for_report\n" +
                "WHERE 1 = 1\n");
        if (dateTime.length() == 10) {
            sbSql.append("AND (jobState <> 1 OR (jobState = 1 AND leaveDate >'" + dateTime + "'))\n");
        } else {
            sbSql.append("AND (jobState <> 1 OR (jobState = 1 AND leaveDate >='" + dateTime + "'))\n");
        }
        sbSql.append("AND (\n" +
                "  userId = '" + userId + "' \n" +
                "\tOR parentId = '" + userId + "'\n" +
                "  OR (\n" +
                "\t\t\t\t\tuserId in(\n" +
                "\t\t\t\t\t\t\t\t\t\tSELECT USER_ID FROM t_sys_user_2_region \n" +
                "\t\t\t\t\t\t\t\t\t\tWHERE \n" +
                "\t\t\t\t\t\t\t\t\t\tREGION_ID in(\n" +
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = '" + userId + "'\n" +
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\tAND v_sys_user_for_report.frontFlag = 1\n" +
                "\t\t\t\t)\n" +
                ")\n" +
                "GROUP BY userId,departName,roleName,userName,regionName \n" +
                "ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser\n" +
                "LEFT JOIN \n" +
                "(\n" +
                "\tSELECT userId,LEFT(reportDate," + dateTime.length() + ") as reportDate,SUM(IF(bxState <> -1,money,0)) as money,\n" +
                "\tSUM(IF(bxState = 1,money,0)) as bxMoney,\n" +
                "\tSUM(IF(bxState = 0,money,0)) as unBxMoney\n" +
                "\tFROM v_wrt_cost_for_report\n");

        sbSql.append("\tGROUP BY userId,LEFT(reportDate," + dateTime.length() + ")\n");
        sbSql.append(") as sysCost ON sysUser.userId = sysCost.userId and sysUser.reportDate = sysCost.reportDate\n");
        sbSql.append("WHERE sysCost.money !=0\n");
        return reportCostDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getForDepartByDepartIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String departId = jsonParams.getString("departId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT sysUser.*,IFNULL(sysCost.money,0) AS money,IFNULL(sysCost.bxMoney,0) AS bxMoney,IFNULL(sysCost.unBxMoney,0) AS unBxMoney\n" +
                "FROM\n" +
                "(\n" +
                "SELECT userId,departName,roleName,userName,regionName,'" + dateTime + "' as reportDate\n" +
                "FROM v_sys_user_for_report\n" +
                "WHERE 1 = 1\n");
        if (dateTime.length() == 10) {
            sbSql.append("AND (jobState <> 1 OR (jobState = 1 AND leaveDate >'" + dateTime + "'))\n");
        } else {
            sbSql.append("AND (jobState <> 1 OR (jobState = 1 AND leaveDate >='" + dateTime + "'))\n");
        }
        sbSql.append("AND departId='" + departId + "'\n" +
                "GROUP BY userId,departName,roleName,userName,regionName \n" +
                "ORDER BY companySortNo,departSortNo,roleSortNo,userSortNo) as sysUser\n" +
                "LEFT JOIN \n" +
                "(\n" +
                "\tSELECT userId,LEFT(reportDate," + dateTime.length() + ") as reportDate,SUM(IF(bxState <> -1,money,0)) as money,\n" +
                "\tSUM(IF(bxState = 1,money,0)) as bxMoney,\n" +
                "\tSUM(IF(bxState = 0,money,0)) as unBxMoney\n" +
                "\tFROM v_wrt_cost_for_report\n");

        sbSql.append("\tGROUP BY userId,LEFT(reportDate," + dateTime.length() + ")\n");
        sbSql.append(") as sysCost ON sysUser.userId = sysCost.userId and sysUser.reportDate = sysCost.reportDate");

        return reportCostDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getForCompanyByUserIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT depart.departId,depart.departName,\n" +
                "IFNULL(cost.money,0) as money ,\n" +
                "IFNULL(cost.bxMoney,0) as bxMoney,\n" +
                "IFNULL(cost.unBxMoney,0) as unBxMoney\n" +
                "FROM (\n" +
                "SELECT \n" +
                "ID as departId,\n" +
                "`NAME` as departName,\n" +
                "CASE \n" +
                "\tWHEN PARENT_ID = '0' THEN SORT_NO * 100 \n" +
                "\tELSE (SELECT (departParent.SORT_NO*100 + t_sys_depart.SORT_NO + 10) FROM t_sys_depart as departParent WHERE t_sys_depart.PARENT_ID = departParent.ID)\n" +
                "END as sortNo\n" +
                "FROM t_sys_depart \n" +
                "WHERE 1 = 1\n" +
                "  AND COMPANY_ID IN ( SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                "         SELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                "         SELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = '" + userId + "'))) \n" +
                "\tAND DEL_FLAG = 0 ORDER BY SORT_NO\n" +
                ") as depart \n" +
                "LEFT JOIN (\n" +
                "\tSELECT t_sys_depart.ID as departId,\n" +
                "  SUM(t_wrt_cost.MONEY) as money,\n" +
                "  SUM(IF(t_wrt_cost.`START`= 1,t_wrt_cost.MONEY,0)) as bxMoney,\n" +
                "  SUM(IF(t_wrt_cost.`START`= 0,t_wrt_cost.MONEY,0)) as unBxMoney\n" +
                "FROM t_wrt_cost\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_cost.W_ID = t_wrt_main.ID\n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_cost.CREATE_USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role on t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tWHERE `START` <> -1 \n" +
                "\t\tAND ((t_wrt_main.FLAGE = 0 AND t_wrt_cost.AUDIT_FLAG is NULL) OR t_wrt_cost.AUDIT_FLAG  = 1) \n" +
                "\t\tAND t_wrt_cost.REPORT_DATE LIKE '" + dateTime + "%'\n" +
                "\t\tAND t_sys_depart.COMPANY_ID in (\n" +
                "         SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                "         SELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(\n" +
                "         SELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID = '" + userId + "')))\n" +
                "\t       GROUP BY t_sys_depart.ID\n" +
                ") as cost \n" +
                "ON depart.departId = cost.departId\n" +
                "ORDER BY depart.sortNo");
        return reportCostDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getForCompanyByCompanyIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String companyId = jsonParams.getString("companyId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT depart.departId,depart.departName,\n" +
                "IFNULL(cost.money,0) as money ,\n" +
                "IFNULL(cost.bxMoney,0) as bxMoney,\n" +
                "IFNULL(cost.unBxMoney,0) as unBxMoney\n" +
                "FROM (\n" +
                "SELECT \n" +
                "ID as departId,\n" +
                "`NAME` as departName,\n" +
                "CASE \n" +
                "\tWHEN PARENT_ID = '0' THEN SORT_NO * 100 \n" +
                "\tELSE (SELECT (departParent.SORT_NO*100 + t_sys_depart.SORT_NO + 10) FROM t_sys_depart as departParent WHERE t_sys_depart.PARENT_ID = departParent.ID)\n" +
                "END as sortNo\n" +
                "FROM t_sys_depart \n" +
                "WHERE 1 = 1\n" +
                "  AND COMPANY_ID = '" + companyId + "' \n" +
                "\tAND DEL_FLAG = 0 ORDER BY SORT_NO\n" +
                ") as depart \n" +
                "LEFT JOIN (\n" +
                "\tSELECT t_sys_depart.ID as departId,\n" +
                "  SUM(t_wrt_cost.MONEY) as money,\n" +
                "  SUM(IF(t_wrt_cost.`START`= 1,t_wrt_cost.MONEY,0)) as bxMoney,\n" +
                "  SUM(IF(t_wrt_cost.`START`= 0,t_wrt_cost.MONEY,0)) as unBxMoney\n" +
                "FROM t_wrt_cost\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_cost.W_ID = t_wrt_main.ID\n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_cost.CREATE_USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role on t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tWHERE `START` <> -1 \n" +
                "\t\tAND ((t_wrt_main.FLAGE = 0 AND t_wrt_cost.AUDIT_FLAG is NULL) OR t_wrt_cost.AUDIT_FLAG  = 1) \n" +
                "\t\tAND t_wrt_cost.REPORT_DATE LIKE '" + dateTime + "%'\n" +
                "\t\tAND t_sys_depart.COMPANY_ID = '" + companyId + "'\n" +
                "\tGROUP BY t_sys_depart.ID\n" +
                ") as cost \n" +
                "ON depart.departId = cost.departId\n" +
                "ORDER BY depart.sortNo\n");
        return reportCostDao.queryForJsonList(sbSql.toString());
    }

    @Override
    public List<JSONObject> getForGroupByUserIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        StringBuilder sbCompanyIds = new StringBuilder();
        StringBuilder sbDepartCompanyIds = new StringBuilder();
        String companysId = common.getAllCompanysIdByUserId(userId);
      /*  List<JSONObject> userList = userDao.getViewJSONListByCondition(" and id = ? ",new Object[]{userId});
        List<String> listCompanyIds = new ArrayList<>();
        for (int i = 0; i < userList.size(); i++) {
             String companyId = userList.get(i).getString("companyId");
             String manageFlag = userList.get(i).getString("manageFlag");
             String companyIds = "";
            if (manageFlag.equals("1")){
                companyDao.getChildrenIds(companyId,listCompanyIds);
                for (int  j= 0; j < listCompanyIds.size(); j++) {
                    companyIds += "'" + listCompanyIds.get(j) + "',";
                }
            }else {
                companyIds +="'"+ companyId+"',";
            }
            if (companyIds.length() > 0) {
                companyIds = companyIds.substring(0, companyIds.length() - 1);
            }
            if (i==0){
                sbCompanyIds.append(" AND ID IN(" + companyIds + ")");
                sbDepartCompanyIds.append("  AND t_sys_depart.COMPANY_ID IN(" + companyIds + ")");
            }else {
                sbCompanyIds.append(" OR ID IN(" + companyIds + ")");
                sbDepartCompanyIds.append("  AND t_sys_depart.COMPANY_ID IN(" + companyIds + ")");
            }
        }*/
        StringBuilder sbSql = new StringBuilder();
        sbSql.append("SELECT company.companyId,company.companyName,\n" +
                "IFNULL(cost.money,0) as money ,\n" +
                "IFNULL(cost.bxMoney,0) as bxMoney,\n" +
                "IFNULL(cost.unBxMoney,0) as unBxMoney\n" +
                "FROM (\n" +
                "SELECT \n" +
                "ID as companyId,\n" +
                "`NAME` as companyName,\n" +
                "CASE \n" +
                "\tWHEN PARENT_ID = '0' THEN SORT_NO * 100 \n" +
                "\tELSE (SELECT (companyParent.SORT_NO*100 + t_sys_company.SORT_NO + 10) FROM t_sys_company as companyParent WHERE t_sys_company.PARENT_ID = companyParent.ID)\n" +
                "END as sortNo\n" +
                "FROM t_sys_company \n" +
                "WHERE 1 = 1\n");
        //sbSql.append(sbCompanyIds.toString());
        sbSql.append("AND ID IN (" + companysId + ")\n");
        sbSql.append("AND DEL_FLAG = 0 \n" +
                ") as company \n" +
                "LEFT JOIN (\n" +
                "\tSELECT t_sys_depart.COMPANY_ID as companyId,\n" +
                "  SUM(t_wrt_cost.MONEY) as money,\n" +
                "  SUM(IF(t_wrt_cost.`START`= 1,t_wrt_cost.MONEY,0)) as bxMoney,\n" +
                "  SUM(IF(t_wrt_cost.`START`= 0,t_wrt_cost.MONEY,0)) as unBxMoney\n" +
                "FROM t_wrt_cost\n" +
                "\tLEFT JOIN t_wrt_main ON t_wrt_cost.W_ID = t_wrt_main.ID\n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_cost.CREATE_USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role on t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID \n" +
                "\tWHERE `START` <> -1 \n" +
                "\t\tAND ((t_wrt_main.FLAGE = 0 AND t_wrt_cost.AUDIT_FLAG is NULL) OR t_wrt_cost.AUDIT_FLAG  = 1) \n" +
                "\t\tAND t_wrt_cost.REPORT_DATE LIKE '" + dateTime + "%'\n");
        //sbSql.append(sbDepartCompanyIds.toString());
        sbSql.append("AND t_sys_depart.COMPANY_ID IN(" + companysId + ")\n");
        sbSql.append("GROUP BY t_sys_depart.COMPANY_ID\n" +
                ") as cost \n" +
                "ON company.companyId = cost.companyId\n" +
                "ORDER BY company.sortNo");

        return reportCostDao.queryForJsonList(sbSql.toString());
    }
    @Override
    public List<JSONObject> getForPersonalSumByUserIdAndDateTime(String jsonData) {
        JSONObject jsonParams = FastJsonConvert.convertJSONToObject(jsonData, JSONObject.class);
        String userId = jsonParams.getString("userId");
        String dateTime = jsonParams.getString("dateTime");
        return  reportCostDao.getForPersonalSumByUserIdAndDateTime(userId,dateTime);
    }

}